package com.ifonly.ar.sql;

import com.ifonly.ar.core.*;
import com.ifonly.ar.dialect.Dialect;
import com.ifonly.ar.exections.SqlExecuteException;
import com.ifonly.ar.helpers.ColumnWithValue;
import com.ifonly.ar.helpers.Pagination;
import com.ifonly.ar.helpers.SqlInfo;
import com.ifonly.ar.tx.ITx;
import com.ifonly.ar.utils.ArrayUtils;
import com.ifonly.ar.utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ifonly
 * @version 1.0 2015-12-03 21:17
 * @since JDK 1.6
 */
public class SqlExecutor extends Executor {
    private static Db db = Db.getInstance();

    private static Record findRecord(SqlInfo sqlInfo) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = db.getThreadLocalConnection();
            rs = query(conn, sqlInfo.getSql(), sqlInfo.getParams());
            if (rs.next()) {
                return RecordResultSetHandler.handle(rs);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                db.closeConnection(conn);
            } catch (SQLException e) {
                //
            }
        }
    }

    private static List<Record> findRecords(String sql, Object... params) {
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = db.getThreadLocalConnection();
            rs = query(conn, sql, params);
            List<Record> records = new ArrayList<Record>();
            while (rs.next()) {
                records.add(RecordResultSetHandler.handle(rs));
            }
            return records;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SqlExecuteException(e);
        } finally {
            try {
                DbUtils.close(rs);
                db.closeConnection(conn);
            } catch (SQLException e) {
                //
            }
        }
    }

    private static ColumnWithValue[] getColumnWithValues(Object[] primaryKeys) {
        if (primaryKeys == null || primaryKeys.length == 0) {
            throw new SqlExecuteException("This method findByPrimaryKeys(ColumnWithValue[] primaryKeys) need the primaryKeys with the primaryKey values");
        }
        int length = primaryKeys.length;
        if (length % 2 != 0) {
            throw new IllegalArgumentException("You can access like (Demo.class, \"ID1\", 1, \"ID2\", 3)");
        }

        ColumnWithValue[] cvs = new ColumnWithValue[length / 2];
        int index = 0;
        for (int i = 0; i < length; ) {
            cvs[index++] = new ColumnWithValue((String) primaryKeys[i], primaryKeys[i + 1]);
            i = i + 2;
        }
        return cvs;
    }

    private static Table getTable(Class<? extends ARModel> clazz, Object[] primaryKeys) {
        if (primaryKeys == null || primaryKeys.length == 0) {
            throw new SqlExecuteException("This method findByPrimaryKeys(ColumnWithValue[] primaryKeys) need the primaryKeys with the primaryKey values");
        }
        int length = primaryKeys.length;
        if (length % 2 != 0) {
            throw new IllegalArgumentException("You can access like (Demo.class, \"ID1\", 1, \"ID2\", 3)");
        }
        Table table = getTableInMapper(clazz);
        String[] pks = table.getPrimaryKeys();
        if (pks.length != length / 2) {
            throw new SqlExecuteException("ARModel[" + clazz.getName() + "] of table [" + table.getTableName() + "] has " + primaryKeys.length + " primaryKey");
        }
        return table;
    }

    public static Record findByPrimaryKeys(Class<? extends ARModel> clazz, ColumnWithValue[] primaryKeys) {
        Table table = getTableInMapper(clazz);
        String tableName = table.getTableName();
        return findByPrimaryKeys(tableName, primaryKeys);
    }

    public static Record findByPrimaryKeys(String tableName, ColumnWithValue[] primaryKeys) {
        SqlInfo sqlInfo = SqlBuilder.buildSelectSql(tableName, primaryKeys);
        return findRecord(sqlInfo);
    }

    public static Record findByPrimaryKeys(Class<? extends ARModel> clazz, String[] columns, ColumnWithValue[] primaryKeys) {
        Table table = getTableInMapper(clazz);
        String tableName = table.getTableName();
        return findByPrimaryKeys(tableName, columns, primaryKeys);
    }

    public static Record findByPrimaryKeys(String tableName, String[] columns, ColumnWithValue[] primaryKeys) {
        SqlInfo sqlInfo = SqlBuilder.buildSelectSql(tableName, columns, primaryKeys);
        return findRecord(sqlInfo);
    }

    public static Record findByPrimaryKeys(Class<? extends ARModel> clazz, Object... primaryKeys) {
        Table table = getTable(clazz, primaryKeys);
        return findByPrimaryKeys(table.getTableName(), primaryKeys);
    }

    public static Record findByPrimaryKeys(Class<? extends ARModel> clazz, String[] columns, Object... primaryKeys) {
        Table table = getTable(clazz, primaryKeys);
        return findByPrimaryKeys(table.getTableName(), columns, primaryKeys);
    }

    public static Record findByPrimaryKeys(String tableName, Object... primaryKeys) {
        ColumnWithValue[] cvs = getColumnWithValues(primaryKeys);
        return findByPrimaryKeys(tableName, cvs);
    }

    public static Record findByPrimaryKeys(String tableName, String[] columns, Object... primaryKeys) {
        ColumnWithValue[] cvs = getColumnWithValues(primaryKeys);
        return findByPrimaryKeys(tableName, columns, cvs);
    }

    public static List<Record> list(Class<? extends ARModel> clazz, String... columns) {
        Table table = getTableInMapper(clazz);
        return list(table.getTableName(), columns);
    }

    public static List<Record> list(String tableName, String... columns) {
        SqlInfo sqlInfo = SqlBuilder.buildSelectSql(tableName, columns, null);
        return list(sqlInfo.getSql(), sqlInfo.getParams());
    }

    public static List<Record> list(String sql, Object... params) {
        return findRecords(sql, params);
    }

    public static Pagination<Record> pagination(Class<? extends ARModel> clazz, int pageNumber, int pageSize) {
        Table table = getTableInMapper(clazz);
        return pagination(table.getTableName(), pageNumber, pageSize, null);
    }

    public static Pagination<Record> pagination(String tableName, int pageNumber, int pageSize) {
        SqlInfo sqlInfo = SqlBuilder.buildSelectSql(tableName, null, null);
        return pagination(sqlInfo.getSql(), pageNumber, pageSize, sqlInfo.getParams());
    }

    public static Pagination<Record> pagination(String sql, int pageNumber, int pageSize, Object... params) {
        Dialect dialect = db.getDialect();
        int start = (pageNumber - 1) * pageSize;
        String dataSql = dialect.sql(sql, start, pageSize);
        String countSql = dialect.count(sql);

        Pagination<Record> pagination = pagination(dataSql, countSql, start, pageSize, params);
        assert pagination != null;
        pagination.setPageNumber(pageNumber);
        pagination.setPageSize(pageSize);
        return pagination;
    }

    private static Pagination<Record> pagination(String dataSql, String countSql, int start, int length, Object... params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = db.getThreadLocalConnection();

            ps = createPreparedStatement(conn, countSql, params);
            rs = ps.executeQuery();
            long count = 0;
            if (rs.next()) {
                Number number = (Number) rs.getObject(Dialect.COUNT_KEY);
                count = number.longValue();
            }
            if (ArrayUtils.isNullOrEmpty(params)) {
                ps = createPreparedStatement(conn, dataSql, start, length);
            } else {
                params = ArrayUtils.merge(params, new Object[]{start, length});
                ps = createPreparedStatement(conn, dataSql, params);
            }

            rs = ps.executeQuery();
            List<Record> records = new ArrayList<Record>();
            while (rs.next()) {
                Record record = RecordResultSetHandler.handle(rs);
                records.add(record);
            }
            return new Pagination<Record>(count, records);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            try {
                DbUtils.close(rs);
                DbUtils.close(ps);
                db.closeConnection(conn);
            } catch (SQLException e) {
                //
            }
        }
    }

    public static void tx(ITx tx) {
        Connection conn = null;
        boolean autoCommit = false;
        try {
            conn = db.getThreadLocalConnection();
            autoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            boolean b = tx.tx();
            if (!b) {
                conn.rollback();
            } else {
                conn.commit();
            }
        } catch (Throwable e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                //
            }
            e.printStackTrace();
            throw new SqlExecuteException(e);
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(autoCommit);
                    db.closeAndRemoveThreadLocalConnection();
                }
            } catch (SQLException e) {
                //
            }
        }
    }

}
